package com.bsh.controller;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.bsh.entity.Dto.EasyStudent;

import java.util.ArrayList;
import java.util.List;

/**
 * 由于表数据量大，因此设计此类用于生成insert语句
 */
public class GeneratesSql {

        public static void main(String[] args) {
        String filePath = "C:\\Users\\cdd\\Desktop\\Java类A卷.xlsx";
        String sheetName = "学生表";
        String tableName = "tb_student";

        // 读取Excel数据
        List<EasyStudent> students = readExcel(filePath, sheetName);

        StringBuilder sqlBuilder = new StringBuilder();
        // 打印读取到的数据
        for (EasyStudent student : students) {
            sqlBuilder.append(generateInsertSql(student, tableName)).append("\r\n");
        }
        System.out.println(sqlBuilder);


    }

    /**
     * 根据excel文件生成insert语句
     * @param student EasyStudent实体
     * @param tableName 表名
     * @return SQL语句
     */
    private static String generateInsertSql(EasyStudent student, String tableName) {
        return "INSERT INTO "+tableName+ "(student_id,class_id,student_name,student_gender,student_birth) VALUES (" +
                "'" + student.getStudentId() + "', " +
                "'" + student.getClassId() + "', " +
                "'" + student.getStudentName() + "', " +
                "'" + student.getStudentGender() + "', " +
                "'"+student.getStudentBirth() +"'"+
                ");";
    }

    /**
     * 读取excel文件
     * @param filePath 文件路径
     * @param sheetName sheet名
     * @return 返回 EasyStudent列表
     */
    private static List<EasyStudent> readExcel(String filePath, String sheetName) {
        ExcelListener excelListener = new ExcelListener();
        EasyExcel.read(filePath, EasyStudent.class, excelListener).sheet(sheetName).doRead();
        return excelListener.getStudents();
    }
}


class ExcelListener extends AnalysisEventListener<EasyStudent>{
    private final List<EasyStudent> students = new ArrayList<>();

    @Override
    public void invoke(EasyStudent student, AnalysisContext analysisContext) {
        students.add(student);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }

    public List<EasyStudent> getStudents() {
        return students;
    }

}

